#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : 全球法律在线项目                #
#                                                                   #
#                   @File Name    : databasecontrol.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2020-12-11 15:04                 #
#                                                                   #
#                   @Last Update  : 2020-12-11 15:04                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes:                                                          #
#                                                                   #
#-------------------------------------------------------------------#
"""
import pymysql


class MysqlDatabaseControl:
    """
    该类是用来进行mysql数据库表操作的
    """

    def __init__(self):
        """
        类方法初始化
        return: None
        """
        pass

    def Gettablename(self, MYSQL_CONNECT):
        """
        获取数据库中的表名
        """
        # 数据库连接
        sqlconn = pymysql.connect(host=MYSQL_CONNECT['MYSQL_HOST'], port=MYSQL_CONNECT['MYSQL_PORT'],
                                  db=MYSQL_CONNECT['MYSQL_DB'], user=MYSQL_CONNECT['MYSQL_USER'],
                                  passwd=MYSQL_CONNECT['MYSQL_PASSWD'], charset='utf8')
        results = []
        try:
            sqlcursor = sqlconn.cursor()
            sql = '''SHOW TABLES'''
            sqlcursor.execute(sql)
            sqlresult = sqlcursor.fetchall()
            for result in sqlresult:
                results.append(result[0])
        finally:
            sqlconn.close()
        return results

    def MatchSqlFile(self, origin_MYSQL_CONNECT, origintable, out_MYSQL_CONNECT, outtable):
        """
        对比两张表的数据元表，将返回在目标表中存在的字段名
        import:oringintable, outtable:数据源表名, 数据目标名
        return:resulttable:第一行存放匹配成功的字段名，第二行存放目标表中在数据源表中不存在的字段名
        """
        origintablefilelist = []  # 存放数据源表的字段
        outtablefilelist = []  # 存放目标表的字段
        resulttablelist = []  # 存放匹配成功的字段
        notablelist = []  # 存放在数据源表中不存在的字段
        # 数据库连接
        originsqlconn = pymysql.connect(host=origin_MYSQL_CONNECT['MYSQL_HOST'],
                                        port=origin_MYSQL_CONNECT['MYSQL_PORT'],
                                        db=origin_MYSQL_CONNECT['MYSQL_DB'], user=origin_MYSQL_CONNECT['MYSQL_USER'],
                                        passwd=origin_MYSQL_CONNECT['MYSQL_PASSWD'], charset='utf8')
        sqlcursor = originsqlconn.cursor()  # 创建数据库游标
        # 获取数据源表中的字段名称
        sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '%s' and table_schema = '%s';" \
              % (origintable, origin_MYSQL_CONNECT['MYSQL_DB'])
        try:
            sqlcursor.execute(sql)
            origintablefileS = sqlcursor.fetchall()
            for origintablefiletuple in origintablefileS:
                origintablefilelist.append(origintablefiletuple[0])
        finally:
            originsqlconn.close()
        # 数据库连接
        outsqlconn = pymysql.connect(host=out_MYSQL_CONNECT['MYSQL_HOST'],
                                     port=out_MYSQL_CONNECT['MYSQL_PORT'],
                                     db=out_MYSQL_CONNECT['MYSQL_DB'],
                                     user=out_MYSQL_CONNECT['MYSQL_USER'],
                                     passwd=out_MYSQL_CONNECT['MYSQL_PASSWD'], charset='utf8')
        # 获取数据元表中的字段名称
        sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '%s' and table_schema = '%s';" \
              % (outtable, out_MYSQL_CONNECT['MYSQL_DB'])
        try:
            sqlcursor.execute(sql)
            outtablefileS = sqlcursor.fetchall()
            for outtablefiletuple in outtablefileS:
                outtablefilelist.append(outtablefiletuple[0])
        finally:
            outsqlconn.close()
        sqlcursor.close()  # 关闭数据库游标
        # 数据源表和数据元表能够匹配的字段名
        for origintablefile in origintablefilelist:
            if origintablefile in outtablefilelist:
                resulttablelist.append(origintablefile)
        # 数据元表在数据源表中不存在的字段名
        for outtablefile in outtablefilelist:
            if outtablefile not in resulttablelist:
                notablelist.append(outtablefile)
        return resulttablelist, notablelist

    def GetSqldata(self, origintable, tablelist, sysidname, sysidflie):
        """
        根据sysid值从数据源表中取出对应字段的记录
        import:origintable, tablelist, sysidname,  sysidflie:数据源表名，匹配的表字段, sysid字段名称，sysid字段内容
        return:resultdict:根据sysidfile从origintable中取出某条记录对应的字段名和值组成的字典
        """
        resultdict = {}  # 存储返回结果的字典
        sqlcursor = self.sqlconn.cursor()  # 创建数据库游标
        sqlfile = ','.join(tablelist)  # 从数据源表中取的字段内容
        sql = "select %s from %s where %s = '%s'" % (sqlfile, origintable, sysidname, sysidflie)
        try:
            sqlcursor.execute(sql)
            sqldatatuple = sqlcursor.fetchall()
            for i in range(len(tablelist)):
                if sqldatatuple[0][i] is None:
                    resultdict[tablelist[i]] = ''
                else:
                    resultdict[tablelist[i]] = sqldatatuple[0][i]
        except Exception as e:
            print('服务器数据库查询' + origintable + '数据源表出错：' + str(e))
        sqlcursor.close()  # 关闭数据库游标
        return resultdict
